package com.pignest.hotel.dao.hibernate;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.pignest.framework.dao.BaseHibernateDao;
import com.pignest.hotel.beans.AdjustAccountHistory;
import com.pignest.hotel.beans.CustomHistory;
import com.pignest.hotel.beans.Living;
import com.pignest.hotel.beans.TbAccounts;
import com.pignest.hotel.beans.TbCloseAccounts;
import com.pignest.hotel.beans.TbLiveGoodsBorrow;
import com.pignest.hotel.dao.InComeDao;
import com.pignest.hotel.definition.InComeItemType;
import com.pignest.hotel.util.PageResult;
import com.pignest.hotel.util.WhereCreator;

public class InComeHibernateDao extends BaseHibernateDao implements InComeDao {
	private String roomName;

	@SuppressWarnings("unchecked")	
	/**查询调帐记录*/
	public List<AdjustAccountHistory> queryAdjustHistory(final String livingId,
			final String accountId,final String roomName,final String name,final Date sAdjustTime,final Date eAdjustTime,
			final String adjustUser )
	{
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from AdjustAccountHistory as b where 1=1");
				if(livingId!=null&&!livingId.equals(""))
					hql.append(" and b.livingId = :LivingId");
				if(accountId!=null&&!accountId.equals(""))
					hql.append(" and b.accountsId =:AccountsId");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.roomName like :RoomName");
				if(name!=null&&!name.equals(""))
					hql.append(" and b.name like :Name");
				if(adjustUser!=null&&!adjustUser.equals(""))
					hql.append(" and b.adjustUser like :AdjustUser");
				if (sAdjustTime != null)
					hql.append(" and b.adjustTime>=:StartAdjustTime");
	            if(eAdjustTime!=null)
	            	hql.append(" and b.adjustTime<=:EndAdjustTime");
				hql.append(" order by b.adjustTime desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				if(livingId!=null&&!livingId.equals(""))
					query.setString("LivingId", livingId);
				if(accountId!=null&&!accountId.equals(""))
					query.setString("AccountsId", accountId);
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", '%' + roomName.replaceAll("%", "%%") + '%');
				if(name!=null&&!name.equals(""))
					query.setString("Name", name);
				if(adjustUser!=null&&!adjustUser.equals(""))
					query.setString("AdjustUser", '%' + adjustUser.replaceAll("%", "%%") + '%');
				
				if (sAdjustTime != null)
					query.setDate("StartAdjustTime", sAdjustTime);
				if (eAdjustTime != null)
					query.setDate("EndAdjustTime", eAdjustTime);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	/***根据多入住查询出账务未结记录**/
    public List<TbAccounts> queryAccoutsByMulLiving(List<Living> list){
    	StringBuilder hql = new StringBuilder();
		hql.append(" from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(list!=null&&list.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:list){
			if(i==list.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(list!=null&&list.size()!=0)
			hql.append(")");
		hql.append(" and b.endstate='否'");
		hql.append(" order by price asc");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
    }
    /***根据单入住查询出账务未结记录**/
    public List<TbAccounts> queryAccountBySingleLiving(Living l){
    	StringBuilder hql = new StringBuilder();
		hql.append(" from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
        hql.append(" and b.living.id='").append(l.getId()).append("'");
		hql.append(" and b.endstate='否'");
		hql.append(" order by price asc");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
    }
   
    /**根据多入住查询统计账务记录
     * @param list 多入住记录
     * @param isCustorm true 则按客人统计false 则按消费项目统计 
     * */
    public List queryAccStaticByMulLiving(List<Living> list,boolean isCustorm)
    {
    	StringBuilder hql = new StringBuilder();
    	hql.append(" select sum(b.price)");
    	if(isCustorm)
    		hql.append(",b.living.custormName");
    	else
    		hql.append(",b.type");
    	
    		
		hql.append(" from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(list!=null&&list.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:list){
			if(i==list.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(list!=null&&list.size()!=0)
			hql.append(")");
		hql.append(" and b.endstate='否'");
		if(isCustorm)
    		hql.append(" group by b.living.custormName");
    	else
    		hql.append("group by b.type");
		List aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
    }
    /**根据多入住查询统计账务记录
     * @param list 多入住记录
     * @param type 1 按消费项目 2 则按客人统计 3 按房间 统计 
     * */
    public List queryAccStaticByMulLiving(List<Living> list,int type,String state)
    {
    	StringBuilder hql = new StringBuilder();
    	hql.append(" select sum(b.price)");
    	if(type==1)
    		hql.append(",b.type");
    	else if(type==2)
       		hql.append(",b.living.custormName");	
    	else
    		hql.append(",b.living.room.name");
    		
		hql.append(" from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(list!=null&&list.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		if(list!=null&&list.size()!=0){
			for(Living l:list){
				if(i==list.size()-1)
				     hql.append("'").append(l.getId()).append("'");
				else
					 hql.append("'").append(l.getId()).append("'").append(",");
				i++;
			}
		}
		if(list!=null&&list.size()!=0)
			hql.append(")");
		if(!state.equals(""))
			   hql.append(" and b.living.liveState='").append(state).append("'");
		//hql.append(" and b.endstate='是'");
		hql.append(" and b.price<0");
		if(type==1)
    		hql.append(" group by b.type");
		else if(type==2)
    		hql.append(" group by b.living.custormName");
		else
			hql.append(" group by b.living.room.name");
		List aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
    }
    /**根据多入住查询统计账务记录
     * @param list 多入住记录
     * @param type 1 按消费项目 2 则按客人统计 3 按房间 统计 
     * */
    public List queryAccStaticByLiving(String livingId,int type,String state)
    {
    	StringBuilder hql = new StringBuilder();
    	hql.append(" select sum(b.price)");
    	if(type==1)
    		hql.append(",b.type");
    	else if(type==2)
       		hql.append(",b.living.custormName");	
    	else
    		hql.append(",b.living.room.name");
    		
		hql.append(" from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(livingId).append("'");
		//hql.append(" and b.endstate='是'");
		if(!state.equals(""))
		   hql.append(" and b.living.liveState='").append(state).append("'");
		hql.append(" and b.price<0");
		if(type==1)
    		hql.append(" group by b.type");
		else if(type==2)
    		hql.append(" group by b.living.custormName");
		else
			hql.append(" group by b.living.room.name");
		
		List aclist=getHibernateTemplate().find(hql.toString());
    	if (aclist == null || aclist.isEmpty())
			return null;
		return aclist;
    }
    /**根据多条入住查找自己的记录
     * 按时间升序排为了查出最高入住时间
     * */
    public List<Living> queryBySelf(List<Living> list)
    {
    	StringBuilder hql = new StringBuilder();
		hql.append(" from Living b where 1=1");
		if(list!=null&&list.size()!=0)
			hql.append(" and b.id in(");
		int i=0;
		for(Living l:list){
			if(i==list.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(list!=null&&list.size()!=0)
			hql.append(")");
		hql.append(" order by b.liveTime desc");
		List<Living> l=getHibernateTemplate().find(hql.toString());
    	if (l == null || l.isEmpty())
			return null;
		return l;
    }
    /**
     * 入住是否有未归还的记录
     * **/
    public List<TbLiveGoodsBorrow> isNoBorrow(List<Living> list){
    	StringBuilder hql = new StringBuilder();
		hql.append(" from TbLiveGoodsBorrow b where 1=1");
		if(list!=null&&list.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:list){
			if(i==list.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(list!=null&&list.size()!=0)
			hql.append(")");
		hql.append(" and b.backtime is  null");
		List<TbLiveGoodsBorrow> l=getHibernateTemplate().find(hql.toString());
    	if (l == null || l.isEmpty())
			return null;
		return l;
	}
    /****************************************************计算金额的相关方法**************************
    /**查询结账时总计的天数*/
    public float queryCheckOutDay(Living l,boolean isCheckOut,List<TbAccounts> acc){
    	float days=0f;
    	StringBuilder hql = new StringBuilder();
		hql.append(" select sum(b.counts) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append( " and b.living.id='").append(l.getId()).append("'");
		hql.append(" and (b.type='").append(InComeItemType.FF.name()).append("'");
		hql.append(" or b.type='").append(InComeItemType.JSFF.name()).append("')");
		if(acc!=null&&acc.size()!=0){
			hql.append(" and b.id in(");
			int i=0;
			for(TbAccounts a:acc){
				if(i==acc.size()-1)
					hql.append("'").append(a.getId()).append("'");
				else
					hql.append("'").append(a.getId()).append("'").append(",");
				i++;
			}
			hql.append(")");
		}
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}
		
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
	  	  Object s= r.get(0);
	  	  if(s!=null)
	  		days=Float.valueOf(s.toString());
	    }
	  return days;
    }
    /**
     * 计算多个入住的消费
     * @param 多个入住
     * @param isPositive true 正数
     * */
    public int countAllConsumsUsed(List<Living> ls,boolean isPositive){
    	int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		if(isPositive)
			return -consum;
		else
			return consum;
    }
    /**计算单个所有入住消费
     * @param living l
     * @param isPositive
     * ***/
    public int countAllConsumsUsed(Living l,boolean isPositive,Boolean isChecked){
    	int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(l.getId()).append("'");
		if(isChecked!=null){
    		if(isChecked==true){
    			hql.append(" and b.endstate='是'");
    		}else
    			hql.append(" and b.endstate='否'");
    	}
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		if(isPositive)
			return -consum;
		else
			return consum;
    }
    /**计算单个所有入住消费
	 * 
	 * @param l 单个入住
	 * @param isPositive true 返回正数 false 反之
	 * @param isCheckOut true 包括已结账的，false 未结账的
	 * ***/
	public int countAllConsums(Living l,boolean isCheckOut,boolean  isPositive )
	{
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(l.getId()).append("'");
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}else
			hql.append(" and b.endstate='否'");
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		if(isPositive)
			return -consum;
		else
			return consum;
		
	}
	/**计算多个所有入住消费
	 * @param ls 多个入住
	 * @param isCheckOut true 包括已结账的，false 未结账的
	 * @param isPositive true 返回正数 false 反之
	 * **/
	public int countAllConsums(List<Living> ls,boolean isCheckOut,boolean isPositive){
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}else
			hql.append(" and b.endstate='否'");
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		if(isPositive)
			return -consum;
		else
			return consum;
	}
    /**计算总存款
     * @param ls多个入住
     * @param isCheckOut 是否结账 
     * 目前的业务没有对存款进行部分结账
     * ***/
	
	public int countAllStorages(List<Living> ls,boolean isCheckOut)
	{
		int storage=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}else
			hql.append(" and b.endstate='否'");
		hql.append(" and b.price>=0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		storage=Integer.valueOf(s.toString());
	    }
		return storage;
	}
	public int countAllStroages(Living l,boolean isCheckOut){
		int storage=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(l.getId()).append("'");
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}else
			hql.append(" and b.endstate='否'");
		hql.append(" and b.price>=0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		storage=Integer.valueOf(s.toString());
	    }
		return storage;
	}
	/**计算押金
	 * @param ls 多个入住
	 * @param isCheckOut 是否结账
	 * ***/
	public int countAllDeposit(List<Living> ls,boolean isCheckOut)
	{
		int deposit=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}else
			hql.append(" and b.endstate='否'");
		hql.append(" and b.type='").append(InComeItemType.YJ.name()).append("'");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		deposit=Integer.valueOf(s.toString());
	    }
		return deposit;
	}
	/**计算押金
	 * @param l 单个入住
	 * @param isCheckOut 是否结账
	 * ***/
	public int countAllDeposit(Living l,boolean isCheckOut){
		int deposit=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(l.getId()).append("'");
		if(isCheckOut){
			hql.append(" and b.endstate='是'");
		}else
			hql.append(" and b.endstate='否'");
		hql.append(" and b.type='").append(InComeItemType.YJ.name()).append("'");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		deposit=Integer.valueOf(s.toString());
	    }
		return deposit;
	}
	/**
	 * 查询已经结账的消费
	 * @param isPositive 是否为正数返回
	 */
	public int countPayed(Living l,boolean isPositive ){
		int payed=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.endstate='是'");
		hql.append(" and b.price<0");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
		if(aclist!=null&&aclist.size()!=0){
			Object s= aclist.get(0);
		  	if(s!=null)
		  		payed=Integer.valueOf(s.toString());
		}
		if(isPositive&&payed<0)
			return -payed;
		return payed;
	}
	/**
	 * 查询单个入住房费
	 * 
	 * */
	public int countRoomConsums(Living l,boolean isPositive){
		int consum =0;
		StringBuffer hql = new StringBuffer();
    	hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
    	hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.YJ.name()).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.WPXF.name()).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.XCK.name()).append("'");
		List list = this.find(hql.toString());
		if (list!=null)
		{
			Object o =list.get(0);
			if(o!=null){
				consum=Integer.valueOf(o.toString());
			}
		}
		if(isPositive)
			return -consum;
		return consum;
	}
	/**
	 * 查询已结账的单个入住房费包括正常房费、调整费、加收房费
	 * 
	 * */
	public int countRoomConsumsChecked(Living l,boolean isPositive,List<TbAccounts> acc){
		int consum =0;
		StringBuffer hql = new StringBuffer();
    	hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
    	hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
    	hql.append(" and ( b.type='").append(InComeItemType.FF.name()).append("'");
    	hql.append("  or b.type='").append(InComeItemType.FJTZF.name()).append("'");
    	hql.append(" or b.type='").append(InComeItemType.JSFF.name()).append("')");
    	hql.append(" and b.endstate='是'");
    	if(acc!=null&&acc.size()!=0){
			hql.append(" and b.id in(");
			int i=0;
			for(TbAccounts a:acc){
				if(i==acc.size()-1)
					hql.append("'").append(a.getId()).append("'");
				else
					hql.append("'").append(a.getId()).append("'").append(",");
				i++;
			}
			hql.append(")");
		}
    	System.out.println(hql.toString());
		List list = this.find(hql.toString());
		if (list!=null)
		{
			Object o =list.get(0);
			if(o!=null){
				consum=Integer.valueOf(o.toString());
			}
		}
		
		if(isPositive)
			return -consum;
		return consum;
	}
	/**
	 * 查询多个入住房费
	 * 
	 * */
	public int countRoomConsums(List<Living> ls,boolean isPositive){
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1 and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		hql.append(" and b.type!='").append(InComeItemType.YJ.name()).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.WPXF.name()).append("'");
    	hql.append(" and b.type!='").append(InComeItemType.XCK.name()).append("'");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		if(isPositive)
			return -consum;
		return consum;
	}
	 /**
     * 查询其它消费除了押金、房费、续存款、加收房费、房间调整费*/
    public int countOtherAccount(Living l,boolean isPositive)
    {
    	int r=0;
    	if(l!=null)
    	{
    		StringBuffer hql = new StringBuffer();
        	hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
        	hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
        	hql.append(" and b.type!='").append(InComeItemType.YJ.name()).append("'");
        	hql.append(" and b.type!='").append(InComeItemType.FF.name()).append("'");
        	hql.append(" and b.type!='").append(InComeItemType.XCK.name()).append("'");
        	hql.append(" and b.type!='").append(InComeItemType.JSFF.name()).append("'");
        	hql.append(" and b.type!='").append(InComeItemType.FJTZF.name()).append("'");
    		List list = this.find(hql.toString());
    		if (list!=null)
    		{
    			Object o =list.get(0);
    			if(o!=null)
    				r= Integer.valueOf(o.toString());
    		}
    
    	}
    	if(isPositive)
           return -r;
    	else
    		return r;
    }
	 /**
     * 按类型查询项目消费*/
    public int countTypeAccount(Living l,boolean isPositive,String type,Boolean isChecked,List<TbAccounts> acc)
    {
    	int r=0;
    	if(l!=null)
    	{
    		StringBuffer hql = new StringBuffer();
        	hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
        	hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
        	hql.append(" and b.type='").append(type).append("'");
        	if(isChecked!=null){
        		if(isChecked==true){
        			hql.append(" and b.endstate='是'");
        		}else
        			hql.append(" and b.endstate='否'");
        	}
        	hql.append(" and b.price<0");
        	if(acc!=null&&acc.size()!=0){
    			hql.append(" and b.id in(");
    			int i=0;
    			for(TbAccounts a:acc){
    				if(i==acc.size()-1)
    					hql.append("'").append(a.getId()).append("'");
    				else
    					hql.append("'").append(a.getId()).append("'").append(",");
    				i++;
    			}
    			hql.append(")");
    		}
    		List list = this.find(hql.toString());
    		if (list!=null)
    		{
    			Object o =list.get(0);
    			if(o!=null)
    				r= Integer.valueOf(o.toString());
    		}
    
    	}
    	if(isPositive)
           return -r;
    	else
    		return r;
    }
    /**
     * 查询余额消费*/
    public int countBalance(Living l,boolean isPositive)
    {
    	int r=0;
    	if(l!=null)
    	{
    		StringBuffer hql = new StringBuffer();
        	hql.append("select sum(b.price) from TbAccounts b where 1=1 ").append(" and b.living.id='").append(l.getId()).append("'");
        	hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
        	hql.append(" and b.endstate='否'");
    		List list = this.find(hql.toString());
    		if (list!=null)
    		{
    			Object o =list.get(0);
    			if(o!=null)
    				r= Integer.valueOf(o.toString());
    		}
    	}
    	if(isPositive&&r<=0)
    		return -r;
    	else
    		return r;
    		
    }
    /**查询当前的多个入住的消费
     * 
     * */
    public int countCurCumsums(List<Living> ls){
    	int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		hql.append(" and b.endstate='否'");
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
	  	  Object s= r.get(0);
	  	  if(s!=null)
	  		consum=Integer.valueOf(s.toString());
	    }
		if(consum<0){
			return -consum;
		}
	  return consum;
    }
	/**单入住下的所有账务包括押金等
	 * @param l
	 * @param isCheckout
	 * */
	public List<TbAccounts> queryAccountsList(Living l,boolean isCheckOut)
	{
			StringBuilder hql = new StringBuilder();
			hql.append(" from TbAccounts b where ");
	    	hql.append(" b.living.id='").append(l.getId()).append("'");
	    	hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
	    	if(isCheckOut){
				hql.append(" and b.endstate='是'");
			}else
				hql.append(" and b.endstate='否'");
	    	List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
	    	if (aclist == null || aclist.isEmpty())
				return null;
			return aclist;
	}
	/**多入住下的所有账务包括押金等
	 * @param l
	 * @param isCheckout
	 * */
	public List<TbAccounts> queryAccountsList(List<Living> ls,boolean isCheckOut)
	{
			StringBuilder hql = new StringBuilder();
			hql.append(" from TbAccounts b where 1=1 ");
			hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
			if(ls!=null&&ls.size()!=0)
				hql.append(" and b.living.id in(");
			int i=0;
			for(Living l:ls){
				if(i==ls.size()-1)
				     hql.append("'").append(l.getId()).append("'");
				else
					 hql.append("'").append(l.getId()).append("'").append(",");
				i++;
			}
			if(ls!=null&&ls.size()!=0)
				hql.append(")");
	    	if(isCheckOut){
				hql.append(" and b.endstate='是'");
			}else
				hql.append(" and b.endstate='否'");
	    	List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
	    	if (aclist == null || aclist.isEmpty())
				return null;
			return aclist;
	}
	
	 public List<TbAccounts> queryAccountUsedList(List<Living> list,boolean isCheckOut){
		   StringBuilder hql = new StringBuilder();
			hql.append(" from TbAccounts b where 1=1");
			hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
			if(list!=null&&list.size()!=0)
				hql.append(" and b.living.id in(");
			int i=0;
			for(Living l:list){
				if(i==list.size()-1)
				     hql.append("'").append(l.getId()).append("'");
				else
					 hql.append("'").append(l.getId()).append("'").append(",");
				i++;
			}
			if(list!=null&&list.size()!=0)
				hql.append(")");
			if(isCheckOut)
			   hql.append(" and b.endstate='是'");
			else
				 hql.append(" and b.endstate='否'");
			hql.append(" and b.price<0");
			hql.append(" order by price asc");
			List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
	   	if (aclist == null || aclist.isEmpty())
				return null;
			return aclist;
		}
	
	 
    public List<TbAccounts> queryAccountUsedList(String livingId,boolean isCheckOut){
	 StringBuilder hql = new StringBuilder();
		hql.append(" from TbAccounts b where 1=1");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(livingId).append("'");
		if(isCheckOut)
			   hql.append(" and b.endstate='是'");
			else
				 hql.append(" and b.endstate='否'");
			hql.append(" and b.price<0");
			hql.append(" order by price asc");
		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
	   	if (aclist == null || aclist.isEmpty())
				return null;
			return aclist;
		}
    public List<TbAccounts> queryAllAccountUsedList(List<Living> list,String state){
		   StringBuilder hql = new StringBuilder();
			hql.append(" from TbAccounts b where 1=1");
			hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
			if(list!=null&&list.size()!=0)
				hql.append(" and b.living.id in(");
			int i=0;
			for(Living l:list){
				if(i==list.size()-1)
				     hql.append("'").append(l.getId()).append("'");
				else
					 hql.append("'").append(l.getId()).append("'").append(",");
				i++;
			}
			if(list!=null&&list.size()!=0)
				hql.append(")");
			if(!state.equals("")){
	   			hql.append(" and b.living.liveState='").append(state).append("'");
	   		}
			hql.append(" and b.price<0");
			hql.append(" order by optime desc");
			List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
	   	if (aclist == null || aclist.isEmpty())
				return null;
			return aclist;
		}
    public List<TbAccounts> queryAllAccountUsedList(String livingId,String state){
   	 StringBuilder hql = new StringBuilder();
   		hql.append(" from TbAccounts b where 1=1");
   		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
   		hql.append(" and b.living.id='").append(livingId).append("'");
   		if(!state.equals("")){
   			hql.append(" and b.living.liveState='").append(state).append("'");
   		}
   			hql.append(" and b.price<0");
   			hql.append(" order by price asc");
   		List<TbAccounts> aclist=getHibernateTemplate().find(hql.toString());
   	   	if (aclist == null || aclist.isEmpty())
   				return null;
   			return aclist;
   		}
    /**计算单个所有消费
	 * @param l 单个入住
	 * @param state 入住状态
	 * @return 返回总消费的正数
	 * ***/
	public int countAllConsumsByState(Living l,String state )
	{
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		hql.append(" and b.living.id='").append(l.getId()).append("'");
		if(!state.equals("")){
   			hql.append(" and b.living.liveState='").append(state).append("'");
   		}
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		
		return -consum;
		
	}
	/**计算多个所有消费
	 * @param ls 多个入住
	 ** @param state 入住状态
	 * @return 返回总消费的正数
	 * **/
	public int countAllConsumsByState(List<Living> ls,String state){
		int consum=0;
		StringBuilder hql = new StringBuilder();
		hql.append("select sum(b.price) from TbAccounts b where 1=1");
		hql.append(" and b.state='").append(TbAccounts.STATE_NORMAL).append("'");
		if(ls!=null&&ls.size()!=0)
			hql.append(" and b.living.id in(");
		int i=0;
		for(Living l:ls){
			if(i==ls.size()-1)
			     hql.append("'").append(l.getId()).append("'");
			else
				 hql.append("'").append(l.getId()).append("'").append(",");
			i++;
		}
		if(ls!=null&&ls.size()!=0)
			hql.append(")");
		if(!state.equals("")){
   			hql.append(" and b.living.liveState='").append(state).append("'");
   		}
		hql.append(" and b.price<0");
		List r = getHibernateTemplate().find(hql.toString());
		if(r!=null&&r.size()!=0){
		  	  Object s= r.get(0);
		  	  if(s!=null)
		  		consum=Integer.valueOf(s.toString());
	    }
		return -consum;
		
	}
	/****************************************************************************************/
	public List<TbCloseAccounts> queryCloseAccPage(final String username,
			final String type, final String orderNo, final String roomName,
			final String roomTypeId,final Integer invoice,final Date sDate,
			final Date eDate, final int start,final int length) {
		// TODO Auto-generated method stub
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from TbCloseAccounts as b where 1=1");
				if(username!=null&&!username.equals(""))
					hql.append(" and b.username like :Name");
				if(orderNo!=null&&!orderNo.equals(""))
					hql.append(" and b.orderNo like :OrderNo");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.living.room.name = :RoomName");
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					hql.append(" and b.living.room.type.id = :RoomTypeId");
				if(type!=null&&!type.equals(""))
					hql.append(" and b.type= :Type");
				if(invoice!=null)
					hql.append(" and b.invoice =:Invoice");
				if (sDate != null)
					hql.append(" and b.time>=:STime");
	            if(eDate!=null)
	            	hql.append(" and b.time<=:ETime");
	            
	            hql.append(" order by b.time desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				
				if(username!=null&&!username.equals(""))
					query.setString("Name", '%' + username.replaceAll("%", "%%") + '%');
				if(orderNo!=null&&!orderNo.equals(""))
					query.setString("OrderNo", '%'+orderNo.replaceAll("%", "%%")+'%');
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", roomName);
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					query.setString("RoomTypeId", roomTypeId);
				if(type!=null&&!type.equals(""))
					query.setString("Type", type);
				if(invoice!=null)
					query.setInteger("Invoice", invoice);
				if (sDate != null)
					query.setDate("STime", sDate);
				if (eDate != null)
					query.setDate("ETime", eDate);
				query.setFirstResult(start);
				query.setMaxResults(length);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}
	public List<TbCloseAccounts> queryCloseAccNoPage(final String username,
			final String type, final String orderNo, final String roomName,
			final String roomTypeId,final Integer invoice,final Date sDate,
			final Date eDate) {
		// TODO Auto-generated method stub
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(org.hibernate.Session session)
					throws HibernateException, SQLException {
				StringBuilder hql = new StringBuilder("from TbCloseAccounts as b where 1=1");
				if(username!=null&&!username.equals(""))
					hql.append(" and b.username like :Name");
				if(orderNo!=null&&!orderNo.equals(""))
					hql.append(" and b.orderNo like :OrderNo");
				if(roomName!=null&&!roomName.equals(""))
					hql.append(" and b.living.room.name = :RoomName");
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					hql.append(" and b.living.room.type.id = :RoomTypeId");
				if(type!=null&&!type.equals(""))
					hql.append(" and b.type= :Type");
				if(invoice!=null)
					hql.append(" and b.invoice =:Invoice");
				if (sDate != null)
					hql.append(" and b.time>=:STime");
	            if(eDate!=null)
	            	hql.append(" and b.time<=:ETime");
	            
	            hql.append(" order by b.time desc");
				org.hibernate.Query query = session.createQuery(hql.toString());
				
				if(username!=null&&!username.equals(""))
					query.setString("Name", '%' + username.replaceAll("%", "%%") + '%');
				if(orderNo!=null&&!orderNo.equals(""))
					query.setString("OrderNo", '%'+orderNo.replaceAll("%", "%%")+'%');
				if(roomName!=null&&!roomName.equals(""))
					query.setString("RoomName", roomName);
				if(roomTypeId!=null&&!roomTypeId.equals(""))
					query.setString("RoomTypeId", roomTypeId);
				if(type!=null&&!type.equals(""))
					query.setString("Type", type);
				if(invoice!=null)
					query.setInteger("Invoice", invoice);
				if (sDate != null)
					query.setDate("STime", sDate);
				if (eDate != null)
					query.setDate("ETime", eDate);
				List<Living> list = query.list();
				return list;
			}
		});
		return list;
	}

}
